﻿USE books
GO

create table Card
(
 CNo int not null,
 Name varchar(50) not null,
 ClassName varchar(20) not null
)
GO
ALTER TABLE Card
ADD
CONSTRAINT PK_Card_CNo PRIMARY KEY(CNo)
create table Books
(
BNo int not null, 
BName varchar(50) not null,
Author varchar(100),
Price  int,
Quantity int
)
GO
ALTER TABLE Books
ADD
CONSTRAINT PK_Books_BNo PRIMARY KEY(BNo)
create table Borrow
(
CNo int not null,
BNo int not null,
RDate datetime not null,
BDate datetime not null
)
GO
ALTER TABLE Borrow
ADD
CONSTRAINT PK_ID primary key(CNo,BNo),
CONSTRAINT FK_Borrow_CNo FOREIGN KEY (CNo)
REFERENCES [Card](CNo),
CONSTRAINT FK_Borrow_BNo FOREIGN KEY (BNo)
REFERENCES Books(BNo)


INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1001,'左文斌','C01');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1002,'程一峰','C01');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1003,'张勇','C01');
INSERT INTO Card(CNo,Name,ClassName)
VALUES (1004,'张国强','C02');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1005,'张青云','C02');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1006,'吕国庆','C02');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1007,'胡忠坤','C03');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1008,'杨咩咩','C03');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1009,'刘重阳','C03');
INSERT INTO Card(CNo,Name,ClassName) 
VALUES (1010,'张进超','C01');

INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (501,'西游记','吴承恩',199,50);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (502,'三国演义','罗贯中',208,68);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (503,'红楼梦','曹雪芹',165,88);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (504,'水浒传','施耐庵',219,59);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (505,'Java数据结构和算法','托尔',16,78);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (506,'SQL Server 从入门到精通','咩咩',18,79);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (507,'Spring Boot实战','丁雪丰',19,80);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (508,'SSM轻量级框架应用实战','肖睿',20,90);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (509,'SSH','补只导',28,20);
INSERT INTO Books(BNo,BName,Author,Price,Quantity ) 
VALUES (510,'MySql','归至道',36,18);


INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1001,504,'2018-05-24','2018-05-20');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1001,505,'2018-05-28','2018-05-25');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1001,506,'2018-05-29','2018-05-24');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1001,509,'2018-05-29','2018-05-25');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1001,508,'2018-05-28','2018-05-23');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1010,507,'2018-05-27','2018-05-25');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1004,508,'2018-05-27','2018-05-25');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1009,508,'2018-05-26','2018-05-23');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1008,501,'2018-05-26','2018-05-29');
INSERT INTO Borrow(CNo,BNo,RDate,BDate) 
VALUES (1007,502,'2018-05-26','2018-05-19');



SELECT CNo,COUNT(BNo) FROM Borrow GROUP BY CNo HAVING COUNT(BNo)>=5;
SELECT Name,ClassName FROM [Card] WHERE CNo IN (SELECT CNo FROM Borrow WHERE BNo=(SELECT BNo FROM Books  WHERE BName='水浒传'));
SELECT * FROM BORROW WHERE RDATE<GETDATE();
SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE BNAME LIKE '%SS%'; 
SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS);



UPDATE Borrow SET RDate=(SELECT  DATEADD(DAY,7,GETDATE())) WHERE CNo IN (SELECT CNo FROM [Card] WHERE ClassName='C01');
DELETE FROM Books WHERE BNo IN (SELECT BNo FROM Books WHERE BNo NOT IN (SELECT BNo FROM Borrow));

 SELECT a.CNO FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME='Java数据结构和算法'AND 
NOT EXISTS(SELECT * FROM BORROW aa,BOOKS bb WHERE aa.BNO=bb.BNO AND bb.BNAME='SQL Server 从入门到精通' AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC;

SELECT CNo FROM Borrow 
WHERE CNo IN (SELECT CNo FROM Borrow 
WHERE BNo=(SELECT BNo FROM Books WHERE BName='Spring Boot实战')) 
AND BNo=(SELECT BNo FROM Books WHERE BName='SSM轻量级框架应用实战') ORDER BY CNo;